﻿Imports System.IO
Imports System.Data.SqlClient
Imports System.Data
Imports System.Web.UI.DataVisualization.Charting

Partial Class Reportes_RPTEdXCursoActual
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Page.IsPostBack = False Then
            'cargar proyectos
            llenarCombos()
        End If
        Label1.Text = ""
        Label2.Text = ""
        Label3.Text = ""


    End Sub


    Protected Sub btnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnConsultar.Click
        cargardatos()
        generarGrafica()
    End Sub
    Sub cargardatos()
        Label1.Text = IIf(ddlDiscapacidad.SelectedValue <> "0", " ( " & ddlDiscapacidad.SelectedItem.Text & ") ", "")
        Label2.Text = IIf(ddlDiscapacidad.SelectedValue <> "0", " ( " & ddlDiscapacidad.SelectedItem.Text & ") ", "")
        Label3.Text = IIf(ddlDiscapacidad.SelectedValue <> "0", "  ( " & ddlDiscapacidad.SelectedItem.Text & ") ", "")

        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("RPTEdCursoActualInFormal", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@codProyecto", ddlProyectos.SelectedValue)
                cmd.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                cmd.Parameters.AddWithValue("@Visita", ddlVisita.SelectedValue)
                cmd.Parameters.AddWithValue("@depto", ddlDepto.SelectedValue)
                cmd.Parameters.AddWithValue("@localizacion", ddlLocalizacion.SelectedValue)
                cmd.Parameters.AddWithValue("@discapacidad", ddlDiscapacidad.SelectedValue)

                Dim reader As IDataReader = cmd.ExecuteReader()

                grdResultado.DataSource = reader
                grdResultado.DataBind()

                reader.NextResult()
                GridView1.DataSource = reader
                GridView1.DataBind()

                reader.NextResult()
                GridView2.DataSource = reader
                GridView2.DataBind()

                reader.NextResult()
                GridView4.DataSource = reader
                GridView4.DataBind()

                reader.NextResult()
                GridView3.DataSource = reader
                GridView3.DataBind()

            End Using

            cnn.Close()

        End Using
    End Sub
    Protected Sub btnExpt_Click(ByVal sender As Object, ByVal e As System.EventArgs) 'Handles btnExpt.Click

    End Sub

    Protected Sub exportarAExcel()


        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As StringWriter = New StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page

        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)

        'form.Controls.Add(tabla)
        form.Controls.Add(Me.grdResultado)
        form.Controls.Add(Me.GridView1)
        form.Controls.Add(Me.GridView2)

        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=Reporte.xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub
    Protected Sub exportarAExcel(ByVal grd As GridView)


        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As StringWriter = New StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page

        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)

        'form.Controls.Add(tabla)
        form.Controls.Add(grd)

        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=Reporte.xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()

    End Sub

    Protected Sub btnExp_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExp.Click
        cargardatos()
        exportarAExcel()
    End Sub

    Sub llenarCombos()
        Dim u As New Web_Usuario
        Using h As New Helper
            u = h.GetUsuario(Context.User.Identity.Name)
        End Using
        Using bd As New BdHogaresDataContext
            Dim a = From i In bd.Web_Proyectos
                    Where (i.web_Operadore.idOperador = u.IdOperador) Or IsNothing(u.IdOperador)
                    Select i.idProyecto, i.CodigoProyecto
            ddlProyectos.DataSource = a
            ddlProyectos.DataValueField = "CodigoProyecto"
            ddlProyectos.DataTextField = "CodigoProyecto"
            ddlProyectos.DataBind()
            ddlProyectos.Items.Insert(0, New ListItem("Todos", "0"))

            cargarDeptos()
            cargarMunicipios()

            Dim b = From i In bd.Web_Parametros
                    Where (i.Categoria = "visitas")
                    Select i.NombreOpcion, i.ValorOpcion

            ddlVisita.DataSource = b
            ddlVisita.DataValueField = "NombreOpcion"
            ddlVisita.DataTextField = "NombreOpcion"
            ddlVisita.DataBind()

        End Using

        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("usp_RPTCruceFiltro", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@campo1", "Localizacion")
                ddlLocalizacion.DataSource = cmd.ExecuteReader
                ddlLocalizacion.DataTextField = "detalle"
                ddlLocalizacion.DataValueField = "valor"
                ddlLocalizacion.DataBind()
                ddlLocalizacion.Items.Insert(0, New ListItem("Todos", "0"))
            End Using
            cnn.Close()
            cnn.Open()



        End Using




    End Sub

    Sub LlenarItems(ByVal objeto As Object, ByVal pregunta As String, ByRef cnn As SqlConnection)
        If pregunta = "sino" Then : objeto.Items.Add(New ListItem("Sí", 1)) : objeto.Items.Add(New ListItem("No", 2)) : Exit Sub : End If
        Using drDatos As SqlDataReader = Funciones.TraerListas(pregunta, cnn)
            With objeto
                .DataSource = drDatos
                .DataTextField = "nombreopcion"
                .DataValueField = "valoropcion"
                .DataBind()
            End With
        End Using
    End Sub
    Sub generarGrafica()

        Using cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("CNNdb").ConnectionString)
            cnn.Open()

            Using cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("RPTEdCursoActualInFormalgraf", cnn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@codProyecto", ddlProyectos.SelectedValue)
                cmd.Parameters.AddWithValue("@codMunicipio", ddlMunicipio.SelectedValue)
                cmd.Parameters.AddWithValue("@Visita", ddlVisita.SelectedValue)
                cmd.Parameters.AddWithValue("@depto", ddlDepto.SelectedValue)
                cmd.Parameters.AddWithValue("@localizacion", ddlLocalizacion.SelectedValue)
                cmd.Parameters.AddWithValue("@discapacidad", ddlDiscapacidad.SelectedValue)

                Dim reader As IDataReader = cmd.ExecuteReader()

                With Chart1
                    .DataSource = reader
                    .ImageType = DataVisualization.Charting.ChartImageType.Png


                    .Series("Default").XValueMember = "Detalle"

                    .Series("Default").YValueMembers = "Cantidad"
                    .DataBind()

                    .Series("Default").ChartType = rbTipoGrafica1.SelectedValue
                    .Series("Default").LabelFormat = "N0"
                    .Series("Default").IsVisibleInLegend = True
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    .Series("Default")("PointWidth") = "0.6"
                    .Series("Default").IsValueShownAsLabel = True
                    .Series("Default")("BarLabelStyle") = "Center"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                    .Series("Default")("DrawingStyle") = "Cylinder"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False

                    .Series("Default").Name = "Personas"
                    .Titles("Title1").Text = " Distribución por sexo " & IIf(ddlDiscapacidad.SelectedValue <> "0", " - " & ddlDiscapacidad.SelectedItem.Text, "")

                    .Legends("Default").LegendStyle = LegendStyle.Table
                    .Legends("Default").Enabled = True
                End With

                reader.NextResult()

                With Chart2
                    .DataSource = reader
                    .ImageType = DataVisualization.Charting.ChartImageType.Png
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False

                    .Legends("Default").LegendStyle = LegendStyle.Table
                    .Legends("Default").Enabled = True

                    .Titles("Title1").Text = "Realizan cursos actualmente (educación informal) " & IIf(ddlDiscapacidad.SelectedValue <> "0", " (mujeres " & ddlDiscapacidad.SelectedItem.Text, "") & ") "

                    With .Series("Default")
                        .XValueMember = "Recibe curso"
                        .YValueMembers = "hombres"
                        .ChartType = RadioButtonList1.SelectedValue
                        .LabelFormat = "N0"
                        .IsVisibleInLegend = True
                        .Item("PointWidth") = "0.6"
                        .IsValueShownAsLabel = True
                        .Item("BarLabelStyle") = "Center"
                        .Item("DrawingStyle") = "Cylinder"
                        .Name = "Hombres"
                    End With

                    .Series.Add("Default2")
                    With .Series("Default2")
                        .XValueMember = "Recibe curso"
                        .YValueMembers = "mujeres"
                        .ChartType = RadioButtonList1.SelectedValue
                        .LabelFormat = "N0"
                        .IsVisibleInLegend = True
                        .Item("PointWidth") = "0.6"
                        .IsValueShownAsLabel = True
                        .Item("BarLabelStyle") = "Center"
                        .Item("DrawingStyle") = "Cylinder"
                        .Name = "Mujeres"
                    End With

                    .DataBind()


                End With

                reader.NextResult()

                With Chart3
                    .DataSource = reader
                    .ImageType = DataVisualization.Charting.ChartImageType.Png


                    .Series("Default").XValueMember = "detalle"

                    .Series("Default").YValueMembers = "mujeres"
                    .DataBind()

                    .Series("Default").ChartType = RadioButtonList2.SelectedValue
                    .Series("Default").LabelFormat = "N0"
                    .Series("Default").IsVisibleInLegend = True
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    .Series("Default")("PointWidth") = "0.6"
                    .Series("Default").IsValueShownAsLabel = True
                    .Series("Default")("BarLabelStyle") = "Center"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                    .Series("Default")("DrawingStyle") = "Cylinder"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False

                    .Series("Default").Name = "Mujeres"
                    .Titles("Title1").Text = "Reciben cursos actualmente (educación informal)" & IIf(ddlDiscapacidad.SelectedValue <> "0", " (mujeres " & ddlDiscapacidad.SelectedItem.Text, "") & ") "

                    .Legends("Default").LegendStyle = LegendStyle.Table
                    .Legends("Default").Enabled = True


                End With

                reader.NextResult()

                With Chart4
                    .DataSource = reader
                    .ImageType = DataVisualization.Charting.ChartImageType.Png


                    .Series("Default").XValueMember = "detalle"

                    .Series("Default").YValueMembers = "hombres"
                    .DataBind()

                    .Series("Default").ChartType = RadioButtonList2.SelectedValue
                    .Series("Default").LabelFormat = "N0"
                    .Series("Default").IsVisibleInLegend = True
                    .ChartAreas("ChartArea1").AxisY.LabelStyle.Format = "N0"
                    .Series("Default")("PointWidth") = "0.6"
                    .Series("Default").IsValueShownAsLabel = True
                    .Series("Default")("BarLabelStyle") = "Center"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = True
                    .Series("Default")("DrawingStyle") = "Cylinder"
                    .ChartAreas("ChartArea1").Area3DStyle.Enable3D = False

                    .Series("Default").Name = "Hombres"
                    .Titles("Title1").Text = "Reciben cursos actualmente (educación informal) " & IIf(ddlDiscapacidad.SelectedValue <> "0", " (hombres " & ddlDiscapacidad.SelectedItem.Text, "") & ") "

                    .Legends("Default").LegendStyle = LegendStyle.Table
                    .Legends("Default").Enabled = True


                End With

            End Using
            cnn.Close()



        End Using




    End Sub


    Protected Sub rbTipoGrafica1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbTipoGrafica1.SelectedIndexChanged, RadioButtonList1.SelectedIndexChanged, RadioButtonList2.SelectedIndexChanged
        generarGrafica()
    End Sub

    'Protected Sub rbTipoGrafica2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles rbTipoGrafica2.SelectedIndexChanged
    '    generarGrafica()
    'End Sub

    Protected Sub LinkButton1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles LinkButton1.Click
        exportarAExcel(GridView3)
    End Sub
#Region "filtros"

    Sub cargarDeptos()
        Using bd As New bdReportesDataContext

            ddlDepto.DataSource = bd.RPT_DepartamentosXProyecto(ddlProyectos.SelectedValue)
            ddlDepto.DataValueField = "Valor"
            ddlDepto.DataTextField = "Detalle"
            ddlDepto.DataBind()
        End Using
    End Sub

    Sub cargarMunicipios()
        Using bd As New bdReportesDataContext

            ddlMunicipio.DataSource = bd.RPT_MunicipiosXProyectoXDepartamento(ddlProyectos.SelectedValue, ddlDepto.SelectedValue)
            ddlMunicipio.DataValueField = "Valor"
            ddlMunicipio.DataTextField = "Detalle"
            ddlMunicipio.DataBind()
        End Using
    End Sub
    Protected Sub ddlProyectos_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlProyectos.SelectedIndexChanged
        cargarDeptos()
        cargarMunicipios()
    End Sub


    Protected Sub ddlDepto_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDepto.SelectedIndexChanged
        cargarMunicipios()
    End Sub
#End Region
End Class
